package sample.utils;

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

/**
 *  EXCEL 工具类（2007|2003）
 *
 * @author zhao.xc
 * @date 2017/5/18 17:58
*/
public class ExcelUtil {
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 字符
	private static DecimalFormat df = new DecimalFormat("0");// 格式化 number String
	private static DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字

    /**
     *  根据文件后缀获取2007|2003的解析器
     */
	public static Workbook getWorkbook(File file) throws Exception {
		String fileName = file.getName();
		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
				.substring(fileName.lastIndexOf(".") + 1);
		FileInputStream fis = new FileInputStream(file);
		// 根据不同的文件名返回不同类型的WorkBook
        switch(extension){
            case "xls":
                return new HSSFWorkbook(fis);
            case "xlsx":
                return new XSSFWorkbook(fis);
            default:
                throw new Exception("不支持该格式的文件！");
        }
	}

	public static void writeExcel(String fileName, List<String> lines) throws IOException {
		Workbook wb = new XSSFWorkbook();
		Font font = wb.createFont();
		font.setFontName("Calibri");
		Sheet sheet = wb.createSheet();
		int rowNo = 0;
		for (String line: lines) {
			String[] cells = line.split(",");
			Row row = sheet.createRow(rowNo++);
			for (int i = 0 ;i < cells.length; i++) {
				row.createCell(i).setCellValue(cells[i].trim());
			}
		}

		FileOutputStream outputStream = null;
		try {
			outputStream = new FileOutputStream(new File(fileName));
			wb.write(outputStream);
		} catch (FileNotFoundException e) {
			throw e;
		} catch (IOException e) {
			throw e;
		} finally {
			try {
				if(outputStream != null) outputStream.close();
				if(wb != null) wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}


	}

    /**
     *  解析 EXCEL 文本
     */
	public static List<List<String>> readExcel(File file,int startSheet,int startRow) {
		List<List<String>> list = new LinkedList<>();
		Workbook wb;
		try {
			wb = getWorkbook(file);
			Sheet sheet = wb.getSheetAt(startSheet);
			String value;
			Row row;
			Cell cell;
			Iterator<Row> rows = sheet.rowIterator();
			while (rows.hasNext()) {
				row = rows.next();
				if(row.getRowNum() >= startRow){
					List<String> cellList = new LinkedList<>();
					Iterator<Cell> cells = row.cellIterator();
					while (cells.hasNext()) {
						cell = cells.next();
						value = cell.getStringCellValue();

						cellList.add(value);
					}
					list.add(cellList);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) {
		//File file = new File("d:\\PanYin.xlsx");
		String fileName = "test.xlsx";
		List<String> lines = Lists.newArrayList();
		lines.add("1,2,3,4,5,6");
		lines.add("Extenternal LINE, KEY");
		try {
			writeExcel(fileName, lines);
		} catch (IOException e) {
			e.printStackTrace();
		}
		/*List<List<Object>> list = readExcel(file, 0, 0);
        for(List<Object> objects : list){
            for(Object object : objects){
                System.out.print(object+"=====");
            }
            System.out.println();
        }*/
	}
}

